USE master;
GO

-- top waits in the system
;WITH Waits AS
	(SELECT
		wait_type,
		wait_time_ms / 1000.0 AS wait_time_s,
		(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_time_s,
        signal_wait_time_ms / 1000.0 AS signal_wait_time_s,
		waiting_tasks_count AS wait_count,
		CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(12,2)) AS wait_percentage,
		ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS row_num
	FROM sys.dm_os_wait_stats WITH (NOLOCK)
	WHERE wait_type NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
		N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'CXCONSUMER',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
		N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', 
		N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 
		N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
		N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',
		N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',
		N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
		N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
		N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
		N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
		N'PREEMPTIVE_OS_WRITEFILE',
		N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
		N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
		N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
		N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
		N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
		N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
		N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
		N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
		N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
		N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
		N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
     ),
RunningWaits AS (
	SELECT
		W1.wait_type,
		CAST(W1.wait_time_s AS DECIMAL(14, 2)) AS wait_time_s,
		CAST(W1.resource_wait_time_s AS DECIMAL(14, 2)) AS resource_wait_time_s,
		CAST(W1.signal_wait_time_s AS DECIMAL(14, 2)) AS signal_wait_time_s,
		W1.wait_count,
		CAST ((W1.wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_wait_time_s,
		CAST ((W1.resource_wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_resource_wait_time_s,
		CAST ((W1.signal_wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_signal_wait_time_s,
		W1.wait_percentage,
		SUM(W1.wait_percentage) OVER(ORDER BY W1.wait_percentage DESC ROWS UNBOUNDED PRECEDING) AS running_wait_percentage
	FROM Waits AS W1)
SELECT
	wait_type, wait_time_s, resource_wait_time_s, signal_wait_time_s, wait_count, wait_percentage, running_wait_percentage, avg_wait_time_s, avg_resource_wait_time_s, avg_signal_wait_time_s
FROM RunningWaits
WHERE running_wait_percentage - wait_percentage <= 95 -- percentage threshold
ORDER BY running_wait_percentage OPTION (RECOMPILE);
 
-- correlate waits with queues|performance counter
-- run performance counter script

-- which databases involve most of the I/O and I/O stalls in the system
;WITH DatabaseIO AS
(
  SELECT
    DB_NAME(fs.database_id) AS database_name,
    mf.type_desc,
    SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) AS io_bytes,
    SUM(fs.io_stall) AS io_stall_ms
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON fs.database_id = mf.database_id
      AND fs.file_id = mf.file_id
  GROUP BY DB_NAME(fs.database_id), mf.type_desc
)
SELECT
	ROW_NUMBER() OVER(ORDER BY io_stall_ms DESC) AS [row_number],
	database_name,
	type_desc, 
	CAST(1. * io_bytes / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
    CAST(io_stall_ms / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
    CAST(100. * io_stall_ms / SUM(io_stall_ms) OVER()
       AS DECIMAL(10, 2)) AS io_stall_percent
FROM DatabaseIO
ORDER BY io_stall_ms DESC OPTION (RECOMPILE);

-- how much memory is going to each database
SELECT
	CASE database_id 
		WHEN 32767 THEN 'ResourceDb' 
		ELSE DB_NAME(database_id) 
    END AS database_name,
	COUNT(*) AS buffered_pages_count, 
	COUNT_BIG(*) * 8192 / (1024 * 1024) AS buffered_pages_mb
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY buffered_pages_count DESC OPTION (RECOMPILE);

-- investigate the top IO database
USE <database_name>;
GO

-- database scoped configuration >= SQL Server 2016
SELECT
	configuration_id,
	name,
	value
FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);

-- query store options for this database >= SQL Server 2016
SELECT
	actual_state_desc,
	desired_state_desc,
	interval_length_minutes,
    current_storage_size_mb,
	max_storage_size_mb, 
	query_capture_mode_desc,
	size_based_cleanup_mode_desc
FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);

-- database automatic tuning options >= SQL Server 2017
SELECT
	[name] AS tuning_option,
	desired_state_desc AS desired_state,
	actual_state_desc AS actual_state,
	reason_desc AS reason
FROM sys.database_automatic_tuning_options OPTION (RECOMPILE);

-- resumable index rebuild operation information >= SQL Server 2017
SELECT
	OBJECT_NAME(object_id) AS table_name,
	index_id,
	name AS index_name,
    sql_text,
	last_max_dop_used,
	partition_number,
	state_desc, 
	start_time,
	percent_complete
FROM sys.index_resumable_operations WITH (NOLOCK)
OPTION (RECOMPILE);

-- query execution statistics with UDFs >= SQL Server 2012
SELECT TOP 25
	DB_NAME() AS database_name,
	qs.last_execution_time,
	qs.execution_count,
	CAST((qs.total_worker_time/1000.0) AS DECIMAL (14, 2)) AS total_worker_time_ms,
	CAST((qs.total_worker_time/(1000.0 * qs.execution_count)) AS DECIMAL (14, 2)) AS avg_worker_time_ms,
	qs.total_logical_reads,
	qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
	qs.total_logical_writes,
	qs.total_logical_writes/qs.execution_count AS avg_logical_writes,
	CAST((qs.total_elapsed_time/1000.0) AS DECIMAL (14, 2)) AS total_elapsed_time_ms,  
	CAST((qs.total_elapsed_time/(1000.0 * qs.execution_count)) AS DECIMAL (14, 2)) AS avg_elapsed_time_ms,
	st.text AS parent_query_text, 
	SUBSTRING(st.[text], qs.statement_start_offset/2 + 1, 
	 (CASE WHEN qs.statement_end_offset = -1 
	   THEN LEN(CONVERT(NVARCHAR(MAX), st.[text])) * 2 
	  ELSE qs.statement_end_offset 
	  END - qs.statement_start_offset)/2) AS query_text,
	qp.query_plan,
	o.type_desc
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
LEFT JOIN sys.objects o ON 
	o.object_id = st.objectid WHERE o.type_desc IN (N'AGGREGATE_FUNCTION', N'SQL_SCALAR_FUNCTION', N'SQL_TABLE_VALUED_FUNCTION', N'SQL_INLINE_TABLE_VALUED_FUNCTION')
ORDER by qs.total_logical_reads DESC;

-- UDF execution statistics >= SQL Server 2016
SELECT
	OBJECT_NAME(object_id, fs.database_id) AS function_name,
	FORMAT(fs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS plan_cached_time,
	fs.last_execution_time,
	fs.execution_count,
	CAST((fs.total_worker_time/1000.0) AS DECIMAL (14, 2)) AS total_worker_time_ms,  
	fs.total_logical_reads,
	fs.total_physical_reads,
	fs.total_logical_writes,
	CAST((fs.total_elapsed_time/1000.0) AS DECIMAL (14, 2)) AS total_elapsed_time_ms,  
	CAST((fs.total_elapsed_time/(1000.0 * fs.execution_count)) AS DECIMAL (14, 2)) AS avg_elapsed_time_ms,
	st.text AS function_text
FROM sys.dm_exec_function_stats fs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
ORDER BY total_worker_time DESC OPTION (RECOMPILE); 

-- I/O statistics by file for the current database
SELECT
	DB_NAME(DB_ID()) AS database_name,
	[file_id],
	num_of_reads,
	num_of_writes,
	num_of_bytes_read,
	num_of_bytes_written,
	CAST(100. * num_of_reads / ( num_of_reads + num_of_writes )
		AS DECIMAL(10,1)) AS reads_percent,
	CAST(100. * num_of_writes / ( num_of_reads + num_of_writes )
		AS DECIMAL(10,1)) AS writes_percent,
	CAST(100. * num_of_bytes_read / ( num_of_bytes_read	+ num_of_bytes_written )
		AS DECIMAL(10,1)) AS read_bytes_percent,
	CAST(100. * num_of_bytes_written / ( num_of_bytes_read	+ num_of_bytes_written )
		AS DECIMAL(10,1)) AS written_bytes_percent
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) OPTION (RECOMPILE);

-- triage based on io_virtual_file_stats
-- run virtualfilestats script

-- data pages of the current database that are currently in the SQL Server buffer pool
SELECT * FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id = DB_ID() OPTION (RECOMPILE);

-- object level inside the database
SELECT TOP 50 
	o.name AS 'object_name',
	i.name AS index_name,
	i.type_desc,
	COUNT(*) AS buffered_pages_count ,
	COUNT_BIG(*) * 8192 / (1024 * 1024) AS buffered_pages_mb
    -- ,o.name, o.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd WITH (NOLOCK)
    INNER JOIN 
    (
        SELECT OBJECT_NAME(object_id) AS name, 
            index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au WITH (NOLOCK)
            INNER JOIN sys.partitions AS p WITH (NOLOCK)
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT OBJECT_NAME(object_id) AS name,   
            index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au WITH (NOLOCK)
            INNER JOIN sys.partitions AS p WITH (NOLOCK) 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS o 
        ON bd.allocation_unit_id = o.allocation_unit_id
LEFT JOIN sys.indexes i WITH (NOLOCK) ON i.object_id = o.object_id AND i.index_id = o.index_id
WHERE bd.database_id = DB_ID()
GROUP BY o.name, o.index_id, i.[name], i.[type_desc]
ORDER BY buffered_pages_count DESC OPTION (RECOMPILE);

-- using sys.dm_exec_query_stats and grouping by query_hash for the top logical I/O queries
;WITH RunningTotals AS
(
  SELECT 
    query_hash,
    SUM(execution_count) AS num_queries,
    SUM(total_logical_reads) AS sum_logical_reads,
    CAST(100. * SUM(total_logical_reads)
              / SUM(SUM(total_logical_reads)) OVER()
          AS DECIMAL(5, 2)) AS reads_percent,
    CAST(100. * SUM(SUM(total_logical_reads)) OVER(ORDER BY SUM(total_logical_reads) DESC
                                              ROWS UNBOUNDED PRECEDING)
              / SUM(SUM(total_logical_reads)) OVER()
          AS DECIMAL(5, 2)) AS reads_running_percent
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  WHERE qs.query_hash <> 0x
    AND qp.dbid = DB_ID()
  GROUP BY query_hash
)
SELECT rt.*,
  (SELECT TOP (1)
     SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
             ((CASE statement_end_offset 
                WHEN -1 THEN DATALENGTH(st.text)
                ELSE qs.statement_end_offset END 
                    - qs.statement_start_offset)/2) + 1
           )
   FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
   WHERE qs.query_hash = rt.query_hash) AS sample_statement
FROM RunningTotals AS rt
WHERE reads_running_percent - reads_percent < 80.00
ORDER BY sum_logical_reads DESC OPTION (RECOMPILE);

-- using sys.dm_exec_query_stats and grouping by query_hash for the top cpu consuming queries
;WITH RunningTotals AS
(
  SELECT 
    query_hash,
    SUM(execution_count) AS num_queries,
    SUM(total_worker_time) AS sum_worker_time,
    CAST(100. * SUM(total_worker_time)
              / SUM(SUM(total_worker_time)) OVER()
          AS DECIMAL(5, 2)) AS cpu_percent,
    CAST(100. * SUM(SUM(total_worker_time)) OVER(ORDER BY SUM(total_worker_time) DESC
                                              ROWS UNBOUNDED PRECEDING)
              / SUM(SUM(total_worker_time)) OVER()
          AS DECIMAL(5, 2)) AS cpu_running_percent
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  WHERE qs.query_hash <> 0x
    AND qp.dbid = DB_ID()
  GROUP BY query_hash
)
SELECT rt.*,
  (SELECT TOP (1)
     SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
             ((CASE statement_end_offset 
                WHEN -1 THEN DATALENGTH(st.text)
                ELSE qs.statement_end_offset END 
                    - qs.statement_start_offset)/2) + 1
           )
   FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
   WHERE qs.query_hash = rt.query_hash) AS sample_statement
FROM RunningTotals AS rt
WHERE cpu_running_percent - cpu_percent < 80.00
ORDER BY sum_worker_time DESC OPTION (RECOMPILE);

-- get the execution plan by query_hash
SELECT 
    qp.query_plan,
	qs.sql_handle, 
    qs.plan_handle, 
    qs.execution_count, 
    qs.total_logical_reads, 
    qs.total_elapsed_time, 
    qp.dbid AS database_id 
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.query_hash = <query_hash> OPTION (RECOMPILE);